# -*- encoding=utf-8 -*-
import datetime
import time

import pandas as pd
import numpy as np


class Checker:

    @classmethod
    def print_types(cls, df):
        """
        返回 dataframe 各列的类型
        :param df:   dataframe
        :return:  {}
        """
        columns = df.columns.values
        result = []

        for col in columns:
            pd_type = df.loc[:, col].dtype

            try:
                se = df.loc[:, col]
                py_type = type(se[se.notna()].iloc[0])

            except IndexError:
                py_type = type(df.loc[:, col].iloc[0])

            res = {"Name": col,
                   "numpy_type": str(pd_type),
                   "python_type": str(py_type)}
            result.append(res)

        return result


    @classmethod
    def list_to_dataframe(cls, datas, columns, pk=None):
        """
        将数据库查询出来的list数据转换为dataframe
        :param datas:       查询结果集， 以list形式展示
        :param columns:     列名, 类型为list
        :param pk:     主键名， 默认为None, 类型为list
        :return:            dataframe
        """

        # 存在主键, 并且主键包含在 列名中，则将 主键列设置为index
        if pk and set(pk).issubset(set(columns)):
            df = pd.DataFrame(datas, columns=columns)
            df.set_index(pk, inplace=True, drop=True)
        else:
            # 对于无主键的数据，进行全排序，方便比较
            df = pd.DataFrame(datas, columns=columns)
            asce = [True for i in range(len(columns))]

            df.sort_values(by=columns, ascending=asce, inplace=True)
            df.reset_index(drop=True, inplace=True)

        return df


    @classmethod
    def check(cls, source, target, num=10):
        """
            检查源与目标的数据一致
            :param source:   源数据
            :param target:   目标数据
            :param num:      保留比对结果中，不匹配结果记录的行数，默认保留10行
            :return:    True/Fasle
        """
        res_pd = cls.diff_dataframe(source, target)

        # 将所有错误的结果集提取出来
        res = res_pd.iloc[((res_pd == False).any(axis=1)).tolist(), :]

        # 提取错误结果，并把源与目标的对应记录提取出来
        if not res.empty:
            res = res.head(num)
            columns = set(res.columns.values)
            index = set(res.index)

            source_col = list(set(source.columns.values).intersection(columns))
            source_index = list(set(source.index).intersection(index))

            source = source.loc[source_index, source_col]

            target_col = list(set(target.columns.values).intersection(columns))
            target_index = list(set(target.index).intersection(index))
            target = target.loc[target_index, target_col]

            return res, source, target
        else:
            return res, None, None

    @classmethod
    def diff_dataframe(cls, source, target):
        """比较两个dataframe，返回两者不相同的单元格

        :param source:
        :param target:
        :return:

        """
        source_columns = set(source.columns.values)
        target_colums = set(target.columns.values)

        # 求并集
        cols = set(source_columns.union(target_colums))

        res_pd = pd.DataFrame()

        df_1 = source.reindex(columns=cols, fill_value=np.nan)
        df_2 = target.reindex(columns=cols, fill_value=np.nan)

        for col in cols:
            try:
                se_1 = df_1[col]
                se_2 = df_2[col]

                # 源 与 目标值 为 null时，需要单独处理
                se_1_na = se_1[se_1.isna()].isna()
                se_2_na = se_2[se_2.isna()].isna()
                
                # NaN 单元格之间的校验
                se_na_diff = cls._series_diff(se_1_na, se_2_na)

                # 结果的校验
                se_1_notna = se_1[se_1.notna()]
                se_2_notna = se_2[se_2.notna()]

                se_not_na_diff = cls._series_diff(se_1_notna, se_2_notna)

                # 将 NaN 校验结果回填到比较结果集中
                se_all = pd.concat([se_not_na_diff, se_na_diff])

                # pd.concat((res_pd, res_series))
                res_pd.insert(0, col, se_all)
            except Exception as ex:
                print(ex)

        return res_pd


    @classmethod
    def _series_diff(cls, se_1, se_2):
        """
        比较两个Series，返回比较结果
        :param se_1:  Series
        :param se_2:  Series
        :return:  Series diff
        """
        c = pd.concat((se_1, se_2), join='outer', axis=1, sort=False)
        c.columns = ['source', 'target']
        return c['source'] == c['target']


    @classmethod
    def _index_intersection(cls, df_1, df_2):
        """
        求两个 datafrome 的index 的交集
        :param df_1:
        :param df_2:
        :return:
        """
        indexes_1 = set(df_1.index)
        indexes_2 = set(df_2.index)
        return list(indexes_1.intersection(indexes_2))

    @classmethod
    def _index_difference(cls, df_1, df_2):
        """
        求两个 datafrome 的index 的差集
        :param df_1:
        :param df_2:
        :return:
        """
        indexes_1 = set(df_1.index)
        indexes_2 = set(df_2.index)
        return list(indexes_1.difference(indexes_2))

    @classmethod
    def add_time(cls, df, h):
        """
        修改dataframe中datatime类型列的时间
        :param df: dataframe
        :param h: 时间，单位小时
        :return:
        """
        h = int(h)
        cols = df.columns.values
        for col in cols:

            value = df.loc[0, col]
            # value = se[list(se.notna())].iloc[0]
            numpy_dtype = df.loc[:, col].dtype

            if numpy_dtype == 'datetime64' or isinstance(value, pd._libs.tslibs.timestamps.Timestamp):
                df.loc[:, col] = df.loc[:, col].apply(lambda x: x + pd.DateOffset(hours=h))
                # df.loc[:, col] = df.loc[:, col].astype('datetime64')

            elif isinstance(value, datetime.datetime):
                df.loc[:, col] = df.loc[:, col].map(lambda x: cls._datetime_add(x, h))
                # df.loc[:, col] = df.loc[:, col].astype('datetime64')

        return df

    @classmethod
    def _datetime_add(cls, x, h):
        try:
            x = x + datetime.timedelta(hours=h)
        except Exception:
            x = np.nan
        # x = x + datetime.timedelta(hours=h)
        return x

    @classmethod
    def print_html_result(cls, res, source, target, table=None, pk=None):
        htmlstr = ''
        columns = set(res.columns.values)
        # ============ 列不相等时的处理 =====================
        source_columns = set(source.columns.values)
        target_columns = set(target.columns.values)

        source_column_diff = columns.difference(source_columns)

        if source_column_diff:
            htmlstr += '<spen style="color:red">源表不存在列： {}</spen>'.format(source_column_diff)
            [columns.remove(col) for col in source_column_diff]
            res = res.loc[:, columns]
            res = res.iloc[((res == False).any(axis=1)).tolist(), :]

        target_column_diff = columns.difference(target_columns)
        if target_column_diff:
            htmlstr += '<spen style="color:red">目标表不存在列： {}</spen>'.format(target_column_diff)
            [columns.remove(col) for col in target_column_diff]
            res = res.loc[:, columns]
            res = res.iloc[((res == False).any(axis=1)).tolist(), :]

        if res.empty:
            return htmlstr

        htmlstr += '<table border="1" cellspacing="0px" style="border-collapse:collapse">'
        if table is not None:
            htmlstr += '<caption>' + table + '</caption>'
        head = '<tr>'
        head += '<th>来源</th>'
        if pk is not None and len(pk) > 0:
            head += '<th>{}</th>'.format(pk)
        else:
            head += '<th>行号</th>'

        new_res = res.head(10)
        # new_res = res
        indexs = new_res.index

        # ============== 行不相等时的处理 ==================
        # 当存在源与目标 行数不相等时，需要执行下面操作, 分别算出存在的行
        source_indexs = cls._index_intersection(new_res, source)
        target_indexs = cls._index_intersection(new_res, target)

        source_diff = source.loc[source_indexs, :]
        target_diff = target.loc[target_indexs, :]

        # 当存在源与目标 行数不相等时，需要执行下面操作， 分别算出是哪些行不相等
        source_index_diff = cls._index_difference(new_res, source)
        target_index_diff = cls._index_difference(new_res, target)

        for col in columns:
            head += '<th>{}</th>'.format(col)

        htmlstr += head + '</tr>'

        for index in indexs:
            row_source = '<tr style="background-color:#c7e5ff"><td>源表</td><td>{}</td>'.format(index)
            row_target = '<tr style="background-color:#eaf5ff"><td>目标表</td><td>{}</td>'.format(index)
            for col in columns:
                style = ''
                if not res.loc[index, col]:
                    style = 'style="color:red"'

                if index in source_index_diff:
                    row_source += '<td style="color:red"></td>'
                else:
                    row_source += '<td  {}>{}</td>'.format(style, source_diff.loc[index, col])

                if index in target_index_diff:
                    row_target += '<td style="color:red"></td>'
                else:
                    row_target += '<td {}>{}</td>'.format(style, target_diff.loc[index, col])

            htmlstr += row_source + '</tr>'
            htmlstr += row_target + '</tr>'
        htmlstr += '</table>'

        return htmlstr
